package gu.sql2java.utils;

import static com.google.common.base.Preconditions.checkArgument;
import static com.google.common.base.Preconditions.checkNotNull;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.google.common.base.MoreObjects;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.google.common.primitives.Ints;
import gu.simplemq.SimpleLog;

/**
 * JDBC工具类
 * @author guyadong
 * @since 3.18.0
 *
 */
public class JDBCUtility {
	private final static ImmutableMap<Integer, Class<?>> sqlTypes;
	static{
		sqlTypes=new ImmutableMap.Builder<Integer, Class<?>>().put(Types.ARRAY,java.sql.Array.class)
				.put(Types.BIGINT,Long.class)
				.put(Types.BINARY,byte[].class)
				.put(Types.BIT,Boolean.class)
				.put(Types.BLOB,byte[].class)
				.put(Types.BOOLEAN,Boolean.class)
				.put(Types.CHAR,String.class)
				.put(Types.CLOB,String.class)
				.put(Types.DATALINK,java.net.URL.class)
				.put(Types.DATE,java.util.Date.class)
				.put(Types.DECIMAL,java.math.BigDecimal.class)
				.put(Types.DISTINCT,Object.class)
				.put(Types.DOUBLE,Double.class)
				.put(Types.FLOAT,Float.class)
				.put(Types.INTEGER,Integer.class)
				.put(Types.JAVA_OBJECT,Object.class)
				.put(Types.LONGVARBINARY,byte[].class)
				.put(Types.LONGVARCHAR,String.class)
				.put(Types.NUMERIC,java.math.BigDecimal.class)
				.put(Types.OTHER,Object.class)
				.put(Types.REAL,Float.class)
				.put(Types.REF,java.sql.Ref.class)
				.put(Types.SMALLINT,Integer.class)
				.put(Types.STRUCT, java.sql.Struct.class)
				.put(Types.TIME, java.util.Date.class)
				.put(Types.TIMESTAMP, java.sql.Timestamp.class)
				.put(Types.TINYINT, Integer.class)
				.put(Types.VARBINARY,byte[].class)
				.put(Types.VARCHAR, String.class)	.build();
	}
	private static Properties defaultConnProperties = new Properties();
	static{
		defaultConnProperties.put("useSSL", "false");
	}
	
	/**
	 * 设置默认的JDBC连接参数,
	 * 为{@code null}忽略,用于为{@link #createConnection(String, Properties)}和
	 * {@link #createConnection(String, String, String, Properties)}指定默认参数
	 * 当前默认值为"useSSL=false"
	 * @param defaultConnProperties
	 */
	public static void setDefaultConnProperties(Properties defaultConnProperties) {
		if(null != defaultConnProperties){
			JDBCUtility.defaultConnProperties = defaultConnProperties;
		}
	}

	/**
	 * @return 返回当前默认的JDBC连接参数
	 */
	public static Properties getDefaultConnProperties() {
		return defaultConnProperties;
	}

	/**
	 * 
	 * @param sqlType SQL Type,see also {@link Types}
	 * @return 返回 SQL Type对应的Java类
	 */
	public static Class<?> getJavaClass(int sqlType) {
		Class<?> clazz = sqlTypes.get(sqlType);
		if(null != clazz){
			return clazz;
		}
		throw new UnsupportedOperationException("Not supported SQL Type yet: " + sqlType);
	}
	
	/**
	 * 查询数据中给定匹配模式的所有表名
	 * @param meta
	 * @param catalog 可为{@code null}
	 * @param schemaPattern schema 支持'%'匹配
	 * @param tableNamePattern 表名,支持'%'匹配
	 * @return catalog为{@code null}则返回 $schema.$tablename 格式的表名列表,否则返回 $catalog.$schema.$tablename 格式的表名列表, 
	 * 	               没有找到返回空表
	 * @throws SQLException
	 */
	public final static List<String> getTablenames(DatabaseMetaData meta,String catalog,String schemaPattern,String tableNamePattern) throws SQLException{
		ImmutableList.Builder<String> builder = new ImmutableList.Builder<>();
		if(null != meta){
			try (ResultSet resultSet = meta.getTables(catalog, schemaPattern, tableNamePattern, new String[]{"TABLE"})){
				while (resultSet.next()) {
					String c = resultSet.getString("TABLE_CAT");
					String s = resultSet.getString("TABLE_SCHEM");
					String t = resultSet.getString("TABLE_NAME");
//					SimpleLog.log("TABLE_CAT={}",s);
//					SimpleLog.log("TABLE_SCHEM={}",s);
//					SimpleLog.log("TABLE_NAME={}",t);
					String qualityName = t;
					if(null != s){
						qualityName = s + "." + qualityName;
					}
					if(null != c){
						qualityName = c + "." + qualityName;
					}
					SimpleLog.log("    table " +qualityName + " found");
					builder.add(qualityName);
				}
			} 
		}
		return builder.build();
	}
	/**
	 * 通过JDBC 接口返回指定表的字段属性
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @param columnLabel 属性名
	 * @param type
	 */
	public final static <T>List<T> getColumnLabel(DatabaseMetaData meta,String catalog,String schematable,String columnLabel,Class<T> type) {
		if (null != meta) {
			String[] nodes = parseSchematable(schematable);
			String schema = nodes[nodes.length-2];
			String tablename = nodes[nodes.length-1];
			try {
				ImmutableList.Builder<T> builder = ImmutableList.builder();
				ResultSet resultSet = checkNotNull(meta, "meta is uninitialized").getColumns(catalog, schema, tablename,
						"%");
				while (resultSet.next()) {
					T column = resultSet.getObject(columnLabel,type);
					builder.add(column);
				}
				resultSet.close();
				return builder.build();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		return null;
	}
	/**
	 * 通过JDBC 接口返回指定表的字段名列表
	 * 
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @return 字段名列表
	 */
	public final static List<String> getColumnNames(DatabaseMetaData meta,String catalog,String schematable) {
		return getColumnLabel(meta,catalog,schematable,"COLUMN_NAME",String.class);
	}
	/**
	 * 通过JDBC 接口返回指定表的字段类型列表
	 * 
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @return 字段名列表
	 */
	public final static List<String> getColumnTypeNames(DatabaseMetaData meta,String catalog,String schematable) {
		return getColumnLabel(meta,catalog,schematable,"TYPE_NAME",String.class);
	}
	/**
	 * 通过JDBC 接口返回指定表的字段的SQL 类型数组,参见{@link java.sql.Types}
	 * 
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @return SQL 类型数组
	 */
	public final static int[] getSqlTypes(DatabaseMetaData meta,String catalog,String schematable) {
		List<Integer> a = getColumnLabel(meta,catalog,schematable,"DATA_TYPE",Integer.class);
		return Ints.toArray(a);
	}
	/**
	 * 通过{@link DatabaseMetaData}返回表所有字段的Java类型
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @param targetTypes
	 * @return 保存所有字段的Java类型的数组
	 */
	public final static Class<?>[] getColumnTypes(DatabaseMetaData meta,String catalog,String schematable,
			Map<String, Class<?>> targetTypes){
		List<String> typenames = getColumnNames(meta,catalog,schematable);
		int[] sqltypes = getSqlTypes(meta,catalog,schematable);
		targetTypes = MoreObjects.firstNonNull(targetTypes, Collections.<String,Class<?>>emptyMap());
		Class<?>[] types = new Class<?>[sqltypes.length];
		for(int i=0; i<types.length; ++i){
			types[i] = MoreObjects.firstNonNull(targetTypes.get(typenames.get(i)),getJavaClass(sqltypes[i]));
		}
		return types;
	}
	/**
	 * 通过{@link ResultSetMetaData}返回表字段名字列表
	 * @param metaData
	 */
	public final static List<String> getColumnNames(ResultSetMetaData metaData){
		try {
			String[] names = new String[metaData.getColumnCount()];
			for(int i=0; i<names.length; ++i){
				names[i] = metaData.getColumnLabel(i + 1);
			}
			return ImmutableList.copyOf(names);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * 通过{@link ResultSetMetaData}返回表字段类型列表
	 * @param metaData
	 */
	public final static List<String> getColumnTypeNames(ResultSetMetaData metaData){
		try {
			String[] names = new String[metaData.getColumnCount()];
			for(int i=0; i<names.length; ++i){
				names[i] = metaData.getColumnTypeName(i + 1);
			}
			return ImmutableList.copyOf(names);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * 通过{@link ResultSetMetaData}返回表字段的SQL类型
	 * @param metaData
	 */
	public static int[] getSqlTypes(ResultSetMetaData metaData){
		try {
			int[] sqlTypes = new int[metaData.getColumnCount()];
			for(int i=0; i<sqlTypes.length; ++i){
				sqlTypes[i] = metaData.getColumnType(i+1); 
			}
			return sqlTypes;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * 通过{@link ResultSetMetaData}返回表字段的Java类型
	 * @param metaData
	 * @param targetTypes
	 * @return 字段的类型数组
	 */
	public final static Class<?>[] getColumnTypes(ResultSetMetaData metaData, 
			Map<String, Class<?>> targetTypes){
		try {
			targetTypes = MoreObjects.firstNonNull(targetTypes, Collections.<String,Class<?>>emptyMap());
			Class<?>[] types = new Class<?>[metaData.getColumnCount()];
			for(int i=0; i<types.length; ++i){
				String columnName = metaData.getColumnLabel(i + 1);
				types[i] = MoreObjects.firstNonNull(
						targetTypes.get(columnName), 
						Class.forName(metaData.getColumnClassName(i+1)));
			}
			return types;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 创建JDBC数据库连接
	 * 
	 * @param url
	 *            数据库连接URL
	 * @param info
	 *            数据库连接配置参数
	 * @return 数据库连接对象
	 * @throws SQLException
	 */
	public static Connection createConnection(String url, Properties info) throws SQLException {
		checkNotNull(info,"info is null").putAll(defaultConnProperties);
		return  DriverManager.getConnection(checkNotNull(url,"url is null"), info);	
	}
	/**
	 * 创建JDBC数据库连接
	 * 
	 * @param url
	 *            数据库连接URL
	 * @param user
	 *            数据库连接用户名
	 * @param password
	 *            数据库连接密码
	 * @param info
	 *            其他数据库连接配置参数,可为{@code null}  
	 * @throws SQLException
	 */
	public static Connection createConnection(String url, String user, String password, Properties info) throws SQLException {
		if(null == info){			
			info = new Properties();
		}
		info.putAll(defaultConnProperties);
		if (null != user) {
			info.put("user", user);
		}
		if (null != password) {
			info.put("password", password);
		}
		return  DriverManager.getConnection(checkNotNull(url,"url is null"), info);	
	}
	/**
	 * JDBC接口执行SQL脚本,允许执行多条SQL语句
	 * 
	 * @param sql
	 *            SQL代码
	 * @param url
	 *            数据库连接URL
	 * @param user
	 *            数据库连接用户名
	 * @param password
	 *            数据库连接密码
	 * @param debug 是否调试输出执行的SQL语句
	 * @return see also {@link Statement#executeUpdate(String)}
	 * @throws SQLException
	 */
	public static int runMultiSQL(String sql, String url, String user, String password, boolean debug) throws SQLException {
		checkArgument(null != sql, "sql is null");
		Connection connection = null;
		Statement stat = null;
		try {
			Properties info = new Properties();			
			// 设置允许执行多条SQL语句
			info.put("allowMultiQueries", "true");
			connection = createConnection(url, user, password, info);
			SimpleLog.log(debug,"sql string:\n" + sql);
			stat = connection.createStatement();
			return stat.executeUpdate(sql);	
		} finally {
			if (null != stat) {
				stat.close();
			}
			if (null != connection) {
				connection.close();
			}
		}
	
	}

	/**
	 * 从 $dbhostname/$schema.$tablename格式字符串中解析出数据库名及表名,
	 * {@code dbhostname}为数据库主机名，当有多个数据库主机连接同一个消息系统时用于在消息频道名中区分不同的数据库主机 
	 * @param schematable
	 */
	public static String[] parseSchematable(String schematable) {
		Pattern p = Pattern.compile("^(?:\\w+/)?(\\w+)\\.(\\w+)$");
		Matcher m = p.matcher(checkNotNull(schematable, "schematable is null"));
		checkArgument(m.find(), "INVALID schematable format,${schema}.${tablename} required");
		return new String[]{ m.group(1),m.group(2)};
	}
	/**
	 * 从 $dbhostname/$schema.$tablename格式字符串中解析出表名
	 * {@code dbhostname}为数据库主机名，当有多个数据库主机连接同一个消息系统时用于在消息频道名中区分不同的数据库主机 
	 * @param schematable
	 */
	public static String parseTablenme(String schematable) {
		Pattern p = Pattern.compile("^(?:\\w+/)?(\\w+)\\.(\\w+)$");
		Matcher m = p.matcher(checkNotNull(schematable, "schematable is null"));
		checkArgument(m.find(), "INVALID schematable format,${schema}.${tablename} required");
		return m.group(2);
	}

	/**
	 * 从数据库连接URL中解析数据库名(schema)字段
	 * 
	 * @param jdbcurl
	 * @return 数据库名(schema)
	 */
	public static String parseSchemaFromJDBCURL(String jdbcurl) {
		Pattern pattern = Pattern.compile("^.+://.+/(\\w+)");
		Matcher matcher = pattern.matcher(checkNotNull(jdbcurl,"jdbcurl is null"));
		checkArgument(matcher.find(), "INVALID jdbc url string: %s", jdbcurl);
		return matcher.group(1);
	}
}
